﻿Imports Entidad
Imports System.Data
Imports System.Data.SqlClient
Public Class BalanzaDatos

    Function listarBalanzas(balanza As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = ""

        If balanza = "" Then
            query = "SELECT BAL_Codigo,BAL_Descripcion " &
                    "FROM Balanza " &
                    "WHERE BAL_Activo = '1'"
        Else
            query = "SELECT BAL_Codigo,BAL_Descripcion " &
                    "FROM Balanza " &
                    "WHERE BAL_Activo = '1' AND BAL_Codigo <> '" & balanza & "'"
        End If

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerPedidosBalanza(codBalanza As String, tipQuery As Integer) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = ""

        If tipQuery = 1 Then
            query = "SELECT dc.DCI_Codigo,dc.DCI_Estado " &
                    "FROM Documento_Cliente_Balanza dcb " &
                    "INNER JOIN Documento_Cliente dc " &
                    "ON dc.DCI_Codigo = dcb.DCI_Codigo AND (dc.DCI_Estado = '07' OR dc.DCI_Estado = '06' OR dc.DCI_Estado = '12') " &
                    "WHERE BAL_Codigo = @BAL " &
                    "AND DCB_Activo = '1'"
        Else
            query = "SELECT DCI_Codigo " &
                    "FROM Documento_Cliente_Balanza " &
                    "WHERE BAL_Codigo = @BAL AND DCB_Activo = '1' " &
                    "AND DCB_Fecha IS NULL"
        End If

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@BAL", codBalanza)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Sub actualizarFechaFinDCB(codPedido As String, balanza As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente_Balanza " &
                              "SET DCB_FechaFin = GETDATE() " &
                              "WHERE DCI_Codigo = @DCI " &
                              "AND BAL_Codigo = @BAL " &
                              "AND DCB_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@DCI", codPedido)
            .Parameters.AddWithValue("@BAL", balanza)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Sub actualizarBalanza(codPedido As String, codBalanza As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query1 As String = "UPDATE dadpub " &
                               "SET dadpub.DADPUB_Balanza = @BAL " &
                               "FROM Documento_Cliente_Almacen_Detalle dcad " &
                               "INNER JOIN Documento_Almacen_Detalle dad " &
                               "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen " &
                               "AND dad.TDOC_Codigo = dcad.TDOC_Almacen AND dad.DCA_Codigo = dcad.DCA_Codigo " &
                               "AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                               "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                               "ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC AND dadp.TDOC_Codigo = dad.TDOC_Codigo " &
                               "AND dadp.DCA_Codigo = dad.DCA_Codigo AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " &
                               "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                               "ON dadpu.CIA = dadp.CIA AND dadpu.SUC_Codigo = dadp.SUC_Codigo AND dadpu.TDOC_Codigo = dadp.TDOC_Codigo " &
                               "AND dadpu.DCA_Codigo = dadp.DCA_Codigo AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " &
                               "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " &
                               "ON dadpub.DADPU_Codigo = dadpu.DADPU_Codigo AND dadpub.DADPUB_Activo = '1' " &
                               "WHERE dcad.DCI_Codigo = @DCI AND dcad.DCAD_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query1
            .Parameters.AddWithValue("@DCI", codPedido)
            .Parameters.AddWithValue("@BAL", codBalanza)
            .ExecuteNonQuery()
        End With

        Dim query2 As String = "UPDATE Documento_Cliente_Balanza " &
                               "SET BAL_Codigo = @BAL, DCB_Fecha = NULL " &
                               "WHERE DCI_Codigo = @DCI " &
                               "AND DCB_Activo = '1'"

        Dim comando1 As New SqlCommand
        With comando1
            .Connection = coneccion
            .CommandText = query2
            .Parameters.AddWithValue("@DCI", codPedido)
            .Parameters.AddWithValue("@BAL", codBalanza)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Sub actualizarFechaBalanza(tipoDocumento As String, codPedido As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query1 As String = "UPDATE Documento_Cliente_Balanza " &
                               "SET DCB_Fecha = GETDATE() " &
                               "WHERE TDOC_Codigo = @TDOC " &
                               "AND DCI_Codigo = @PED AND DCB_Activo = '1' AND DCB_Fecha IS NULL"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query1
            .Parameters.AddWithValue("@TDOC", tipoDocumento)
            .Parameters.AddWithValue("@PED", codPedido)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

End Class
